﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Mulberry.Model;
using OMG.Fill.Mulberry.Model.Ent;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;

namespace OMG.Fill.Mulberry.Service
{
    /// <summary>
    /// 表6蚕桑综合利用情况调查表T_MulComplexQuest通用操作类
    /// </summary>
    public class MulComplexQuestServices:MulComplexQuests
    {
        ReportServices rs = new ReportServices();
        public Pager<List<MulComplexQuestEntity>> GetPageList(MulComplexQuestQuery query)
        {
            Pager<List<MulComplexQuestEntity>> obj = new Pager<List<MulComplexQuestEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.T_MulComplexQuest where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<MulComplexQuestEntity>(strSql,paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal,paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取表6蚕桑综合利用情况调查表T_MulComplexQuest分页列表失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 获取指定对象
        /// </summary>
        public override ResResult<MulComplexQuestEntity> GetModel(string ReportId)
        {
            ResResult<MulComplexQuestEntity> obj = new ResResult<MulComplexQuestEntity>();
            MulComplexQuestEntity model = new MulComplexQuestEntity();
            if (CheckRepCount(ReportId))
            {
                model.ReportId = ReportId;
                if (AddInfo(model).status > 0)
                {
                    obj.model = model;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                }
            }
            else
            {
                string strSql = @"select * from dbo.T_MulComplexQuest where ReportId = @ReportId";
                try
                {
                    using (var dbCon = ConnectFactory.GetOpenConnection())
                    {
                        obj.model = dbCon.Query<MulComplexQuestEntity>(strSql, new { ReportId = ReportId }).FirstOrDefault();
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "获取成功！";
                        dbCon.Dispose();//数据库连接释放
                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("获取表1桑园面积统计表T_MulComplexQuest对象信息失败", ex);

                }
            }
            return obj;
        }
        /// <summary>
        /// 判定是否生成过报表
        /// </summary>
        /// <param name="reportId"></param>
        /// <returns>没生成过true,否则false</returns>
        public bool CheckRepCount(string reportId)
        {
            string strSql = @"select count(1) from dbo.T_MulComplexQuest where ReportId=@ReportId";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportId = reportId }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("判断是否生成过表1桑园面积统计表T_MulComplexQuest失败", ex);
                return false;
            }
        }
        public ResResult<int> UpdateInfo(MulComplexInfoModel model)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"UPDATE dbo.T_MulComplexQuest set ";
            strSql += "ReportId = @ReportId,";
            strSql += "MulFunWeight = @MulFunWeight,";
            strSql += "MulFunValue = @MulFunValue,";
            strSql += "MulTeaWeight = @MulTeaWeight,";
            strSql += "MulTeaValue = @MulTeaValue,";
            strSql += "MulFruitWeight = @MulFruitWeight,";
            strSql += "MulFruitValue = @MulFruitValue,";
            strSql += "MulExcreWeight = @MulExcreWeight,";
            strSql += "MulExcreValue = @MulExcreValue,";
            strSql += "MulFertWeight = @MulFertWeight,";
            strSql += "MulFertValue = @MulFertValue,";
            strSql += "MulPupaeWeight = @MulPupaeWeight,";
            strSql += "MulPupaeValue = @MulPupaeValue,";
            strSql += "MulWineWeight = @MulWineWeight,";
            strSql += "MulWineValue = @MulWineValue,";
            strSql += "OtherWeight = @OtherWeight,";
            strSql += "OtherValue = @OtherValue";
            strSql += " where Id = @Id";

            using (var dbCon = ConnectFactory.GetOpenConnection())
            {
                var trans = dbCon.BeginTransaction();
                try
                {
                    int result = dbCon.Execute(strSql, model, trans);
                    string strRep = "update dbo.T_Report set Status='2',UpdateTime=@UpdateTime where Id=@ReportId";
                    dbCon.Execute(strRep, new { ReportId = model.ReportId, UpdateTime = DateTime.Now }, trans);
                    //提交事务
                    trans.Commit();
                    obj.model = result;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "更新成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
                catch (Exception ex)
                {
                    //出现异常，事务Rollback
                    trans.Rollback();
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("更新表2季度生产情况表T_MulComplexQuest对象信息失败", ex);
                    return obj;
                }
            }
        }

        /// <summary>
        /// 根据reportId获取id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public virtual ResResult<MulAreaStatistEntity> GetModelId(string ReportId)
        {
            ResResult<MulAreaStatistEntity> obj = new ResResult<MulAreaStatistEntity>();
            string strSql = @"select * from dbo.T_MulComplexQuest where ReportId = @ReportId";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.model = dbCon.Query<MulAreaStatistEntity>(strSql, new { ReportId = ReportId }).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取表T_MulComplexQuest对象信息失败", ex);
                return obj;
            }
        }


        /// <summary>
        /// 获取今往年数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<MulComplexViewModel> GetModel(MulNowModel model)
        {
            ResResult<MulComplexViewModel> obj = new ResResult<MulComplexViewModel>();
            MulComplexQuestEntity vfEntity = new MulComplexQuestEntity();
            MulComplexViewModel vvm = new MulComplexViewModel();
            DictServices dictServices = new DictServices();
            var paramlist = new DynamicParameters();
            string strSqlReport = "";
            if (model != null)
            {
                if (!string.IsNullOrEmpty(model.AreaCode) && !string.IsNullOrEmpty(model.ReportCode) && !string.IsNullOrEmpty(model.FillInYear))
                {
                    string FillInYear = dictServices.GetKey(model.FillInYear);
                    paramlist.Add("AreaCode", model.AreaCode);
                    paramlist.Add("ReportCode", model.ReportCode);
                    paramlist.Add("FillInYear", FillInYear);
                    int OldFillInYear = Convert.ToInt32(FillInYear) - 1;
                    if (OldFillInYear < 0)
                    {
                        OldFillInYear = 0;
                    }
                    paramlist.Add("OldFillInYear", OldFillInYear.ToString());
                    string strNowSql = "SELECT mcq.Id,mcq.ReportId,MulExcreValue,MulExcreWeight,MulFertValue,MulFertWeight,MulFruitValue,MulFruitWeight,MulFunValue,MulFunWeight,MulPupaeValue,MulPupaeWeight,MulTeaValue,MulTeaWeight,MulWineValue,MulWineWeight,OtherValue,OtherWeight,ReportCode,Status as ReportStatus "
                        + " FROM T_Report r LEFT JOIN T_MulComplexQuest mcq ON mcq.ReportId = r.Id WHERE ReportCode = @ReportCode and FillInYear = @FillInYear and AreaCode = @AreaCode ";
                    string strOldSql = "SELECT mcq.Id as OldId,mcq.ReportId as OldReportId,MulExcreValue as OldMulExcreValue,MulExcreWeight as OldMulExcreWeight,MulFertValue as OldMulFertValue,MulFertWeight as OldMulFertWeight,MulFruitValue as OldMulFruitValue,MulFruitWeight as OldMulFruitWeight,MulFunValue as OldMulFunValue,MulFunWeight as OldMulFunWeight,"
                        + "MulPupaeValue as OldMulPupaeValue,MulPupaeWeight as OldMulPupaeWeight,MulTeaValue as OldMulTeaValue,MulTeaWeight as OldMulTeaWeight,MulWineValue as OldMulWineValue,MulWineWeight as OldMulWineWeight,OtherValue as OldOtherValue,OtherWeight As OldOtherWeight,ReportCode,Status as OldReportStatus "
                        + " FROM T_Report r LEFT JOIN T_MulComplexQuest mcq ON mcq.ReportId = r.Id WHERE ReportCode = @ReportCode and FillInYear = @OldFillInYear and AreaCode = @AreaCode ";
                    strSqlReport = "SELECT * FROM ((" + strNowSql + ")t left JOIN  (" + strOldSql + ")tt ON t.ReportCode = tt.ReportCode)";

                    Boolean currentResult = rs.CheckRepCreate(model.ReportCode, model.AreaCode, FillInYear);
                    Boolean OldResult = rs.CheckRepCreate(model.ReportCode, model.AreaCode, OldFillInYear.ToString());
                    //判断选择的年度与上一年度是否生成过报表
                    if (currentResult && OldResult)
                    {
                        obj.model = vvm;
                        obj.status = -1;
                        obj.message = model.FillInYear + "与上一年的报表没有填报数据！！";
                    }
                    //判断本年是否生成过报表
                    else if (currentResult)
                    {
                        var dbCon = ConnectFactory.GetOpenConnection();
                        obj.model = dbCon.Query<MulComplexViewModel>(strOldSql, paramlist).FirstOrDefault();
                        obj.status = -2;
                        obj.message = model.FillInYear + "的报表没有填报数据！！";
                        dbCon.Dispose();//数据库连接释放
                    }
                    else
                    {
                        var dbCon = ConnectFactory.GetOpenConnection();
                        //获取报表Id
                        vfEntity.ReportId = rs.GetReportId(model.ReportCode, model.AreaCode, FillInYear);
                        //判断表T_VegFacility是否初始化过
                        if (CheckRepCount(vfEntity.ReportId))
                        {
                            if (AddInfo(vfEntity).status > 0)//报表初始化
                            {
                                //查询去年数据
                                obj.model = dbCon.Query<MulComplexViewModel>(strOldSql, paramlist).FirstOrDefault();
                                obj.status = 1;//初始化成功status为1
                                obj.message = "MulAreaStatist报表初始化成功！";
                                return obj;
                            }
                        }
                        else
                        {
                            try
                            {
                                obj.model = dbCon.Query<MulComplexViewModel>(strSqlReport, paramlist).FirstOrDefault();
                                obj.status = 2;//成功状态为2
                                obj.message = "获取MulAreaStatist报表今往年数据成功！";
                                return obj;
                            }
                            catch (Exception ex)
                            {
                                obj.status = 0; //失败状态
                                obj.message = ex.ToString().Trim();
                                LogHelper.ErrorLog("获取MulAreaStatist报表今往年数据失败", ex);
                            }
                        }
                    }
                }
            }
            return obj;
        }

        /// <summary>
        /// 读取导入的Excel数据
        /// </summary>
        /// <param name="pathName">导入文件路径</param>
        /// <param name="fileSuffix">文件后缀</param>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="startRow">从第几行开始读取数据</param>
        /// <returns></returns>
        public List<MulComplexInfoModel> ExcelToModel(string pathName, string fileSuffix, string sheetName, int startRow)
        {

            IWorkbook workbook = null;
            ISheet sheet = null;
            List<MulComplexInfoModel> list = new List<MulComplexInfoModel>();
            FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
            switch (fileSuffix)
            {
                case ".xls":
                    workbook = new HSSFWorkbook(fs);
                    break;
                case ".xlsx":
                    workbook = new XSSFWorkbook(fs);
                    break;
                default:
                    workbook = new HSSFWorkbook(fs);
                    break;
            }
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
                if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                {
                    sheet = workbook.GetSheetAt(0);
                }
            }
            else
            {
                sheet = workbook.GetSheetAt(0);
            }
            if (sheet != null)
            {
                //获取有数据的行数
                int rowCount = 0;
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        rowCount = sheet.GetRow(i).RowNum;
                    }
                    else
                    {
                        break;
                    }
                }
                for (int i = startRow; i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    MulComplexInfoModel model = new MulComplexInfoModel();
                    if (row.GetCell(0) != null && row.GetCell(0).ToString() != "")
                    {
                        model.MulFunWeight = decimal.Parse(row.GetCell(0).ToString());
                    }
                    else
                    {
                        model.MulFunWeight = null;
                    }
                    if (row.GetCell(1) != null && row.GetCell(1).ToString() != "")
                    {
                        model.MulFunValue = decimal.Parse(row.GetCell(1).ToString());
                    }
                    else
                    {
                        model.MulFunValue = null;
                    }
                    if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                    {
                        model.MulTeaWeight = decimal.Parse(row.GetCell(2).ToString());
                    }
                    else
                    {
                        model.MulTeaWeight = null;
                    }
                    if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                    {
                        model.MulTeaValue = decimal.Parse(row.GetCell(3).ToString());
                    }
                    else
                    {
                        model.MulTeaValue = null;
                    }
                    if (row.GetCell(4) != null && row.GetCell(4).ToString() != "")
                    {
                        model.MulFruitWeight = decimal.Parse(row.GetCell(4).ToString());
                    }
                    else
                    {
                        model.MulFruitWeight = null;
                    }
                    if (row.GetCell(5) != null && row.GetCell(5).ToString() != "")
                    {
                        model.MulFruitValue = decimal.Parse(row.GetCell(5).ToString());
                    }
                    else
                    {
                        model.MulFruitValue = null;
                    }
                    if (row.GetCell(6) != null && row.GetCell(6).ToString() != "")
                    {
                        model.MulExcreWeight = decimal.Parse(row.GetCell(6).ToString());
                    }
                    else
                    {
                        model.MulExcreWeight = null;
                    }
                    if (row.GetCell(7) != null && row.GetCell(7).ToString() != "")
                    {
                        model.MulExcreValue = decimal.Parse(row.GetCell(7).ToString());
                    }
                    else
                    {
                        model.MulExcreValue = null;
                    }
                    if (row.GetCell(8) != null && row.GetCell(8).ToString() != "")
                    {
                        model.MulFertWeight = decimal.Parse(row.GetCell(8).ToString());
                    }
                    else
                    {
                        model.MulFertWeight = null;
                    }
                    if (row.GetCell(9) != null && row.GetCell(9).ToString() != "")
                    {
                        model.MulFertValue = decimal.Parse(row.GetCell(9).ToString());
                    }
                    else
                    {
                        model.MulFertValue = null;
                    }
                    if (row.GetCell(10) != null && row.GetCell(10).ToString() != "")
                    {
                        model.MulPupaeWeight = decimal.Parse(row.GetCell(10).ToString());
                    }
                    else
                    {
                        model.MulPupaeWeight = null;
                    }
                    if (row.GetCell(11) != null && row.GetCell(11).ToString() != "")
                    {
                        model.MulPupaeValue = decimal.Parse(row.GetCell(11).ToString());
                    }
                    else
                    {
                        model.MulPupaeValue = null;
                    }
                    if (row.GetCell(12) != null && row.GetCell(12).ToString() != "")
                    {
                        model.MulWineWeight = decimal.Parse(row.GetCell(12).ToString());
                    }
                    else
                    {
                        model.MulWineWeight = null;
                    }
                    if (row.GetCell(13) != null && row.GetCell(13).ToString() != "")
                    {
                        model.MulWineValue = decimal.Parse(row.GetCell(13).ToString());
                    }
                    else
                    {
                        model.MulWineValue = null;
                    }
                    if (row.GetCell(14) != null && row.GetCell(14).ToString() != "")
                    {
                        model.OtherWeight = decimal.Parse(row.GetCell(14).ToString());
                    }
                    else
                    {
                        model.OtherWeight = null;
                    }
                    if (row.GetCell(15) != null && row.GetCell(15).ToString() != "")
                    {
                        model.OtherValue = decimal.Parse(row.GetCell(15).ToString());
                    }
                    else
                    {
                        model.OtherValue = null;
                    }
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 添加
        /// </summary>
        public virtual ResResult<int> AddInfo(MulComplexInfoModel model)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"INSERT INTO dbo.T_MulComplexQuest";
            strSql += "(";
            strSql += "Id,";
            strSql += "ReportId,";
            strSql += "MulFunWeight,";
            strSql += "MulFunValue,";
            strSql += "MulTeaWeight,";
            strSql += "MulTeaValue,";
            strSql += "MulFruitWeight,";
            strSql += "MulFruitValue,";
            strSql += "MulExcreWeight,";
            strSql += "MulExcreValue,";
            strSql += "MulFertWeight,";
            strSql += "MulFertValue,";
            strSql += "MulPupaeWeight,";
            strSql += "MulPupaeValue,";
            strSql += "MulWineWeight,";
            strSql += "MulWineValue,";
            strSql += "OtherWeight,";
            strSql += "OtherValue)";
            strSql += " values(";
            strSql += "@Id,";
            strSql += "@ReportId,";
            strSql += "@MulFunWeight,";
            strSql += "@MulFunValue,";
            strSql += "@MulTeaWeight,";
            strSql += "@MulTeaValue,";
            strSql += "@MulFruitWeight,";
            strSql += "@MulFruitValue,";
            strSql += "@MulExcreWeight,";
            strSql += "@MulExcreValue,";
            strSql += "@MulFertWeight,";
            strSql += "@MulFertValue,";
            strSql += "@MulPupaeWeight,";
            strSql += "@MulPupaeValue,";
            strSql += "@MulWineWeight,";
            strSql += "@MulWineValue,";
            strSql += "@OtherWeight,";
            strSql += "@OtherValue)";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    model.Id = CustomFun.GetGuid();
                    int result = dbCon.Execute(strSql, model);
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "添加成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "添加失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("新增表6蚕桑综合利用情况调查表T_MulComplexQuest对象信息失败", ex);
                return obj;
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        public ResResult<int> DeleteData(string id)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"delete from dbo.T_MulComplexQuest where ReportId = @Id";
            int result;
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    result = dbCon.Execute(strSql, new { Id = id });
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "删除成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "删除失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("删除表6蚕桑综合利用情况调查表T_MulComplexQuest对象信息失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 全年桑综合利用情况统计------(按年统计)
        /// </summary>
        public ResResult<List<MulComplexStatsModel>> GetStatsList(MulNowModel model)
        {
            DictServices dictServices = new DictServices();
            string ProvinceCode = "530000000000";
            if (!string.IsNullOrEmpty(model.FillInYear))
            {
                model.FillInYear = dictServices.GetPZKey(model.FillInYear, "ReportYear");
            }
            ResResult<List<MulComplexStatsModel>> result = new ResResult<List<MulComplexStatsModel>>();
            //县级统计代码
            string StrAreaSql = "select Area.Code AreaCode,Dict.DictText FillYear, Area.FullName AreaName,Coord";
            StrAreaSql += ", ISNULL(MulFunWeight,0.00) MulFunWeight,ISNULL( MulFunValue,0.00) MulFunValue, ISNULL(MulTeaWeight,0.00) MulTeaWeight,  ISNULL(MulTeaValue,0.00) MulTeaValue, ISNULL(MulFruitWeight,0.00) MulFruitWeight   ";
            StrAreaSql += ",ISNULL(MulFruitValue,0.00) MulFruitValue, ISNULL(MulExcreWeight,0.00) MulExcreWeight, ISNULL(MulExcreValue,0.00) MulExcreValue, ISNULL(MulFertWeight,0.00) MulFertWeight, ISNULL(MulFertValue,0.00) MulFertValue ";
            StrAreaSql += ",ISNULL(MulPupaeWeight,0.00) MulPupaeWeight, ISNULL(MulPupaeValue,0.00) MulPupaeValue, ISNULL(MulWineWeight,0.00) MulWineWeight, ISNULL(MulWineValue,0.00) MulWineValue, ISNULL(OtherWeight,0.00) OtherWeight , ISNULL(OtherValue,0.00) OtherValue   ";
            StrAreaSql += "from(select   ";
            StrAreaSql += "case when grouping(Area.Code) = 1 then @AreaCode else Code end Code,   ";
            StrAreaSql += "SUM(ISNULL(MulComplexQuest.MulFunWeight, 0.00))  MulFunWeight,   ";// --桑枝菌重量
            StrAreaSql += "SUM(ISNULL(MulComplexQuest.MulFunValue, 0.00)) MulFunValue,   ";// --桑枝菌产值
            StrAreaSql += "SUM(ISNULL(MulComplexQuest.MulTeaWeight, 0.00)) MulTeaWeight,   ";// --桑叶茶、菜重量
            StrAreaSql += "SUM(ISNULL(MulComplexQuest.MulTeaValue, 0.00)) MulTeaValue,   ";// --桑叶茶、菜产值
            StrAreaSql += "SUM(ISNULL(MulComplexQuest.MulFruitWeight, 0.00))  MulFruitWeight,   ";// --桑果、汁、酒重量
            StrAreaSql += "SUM(ISNULL(MulComplexQuest.MulFruitValue, 0.00)) MulFruitValue,   ";// --桑果、汁、酒产值
            StrAreaSql += "SUM(ISNULL(MulComplexQuest.MulExcreWeight, 0.00)) MulExcreWeight,   ";// --蚕沙提取叶绿素重量
            StrAreaSql += "SUM(ISNULL(MulComplexQuest.MulExcreValue, 0.00)) MulExcreValue,    "; //--蚕沙提取叶绿素产值
            StrAreaSql += "SUM(ISNULL(MulComplexQuest.MulFertWeight, 0.00)) MulFertWeight,    "; //--蚕沙肥料重量
            StrAreaSql += "SUM(ISNULL(MulComplexQuest.MulFertValue, 0.00)) MulFertValue,    "; //--蚕沙肥料产值
            StrAreaSql += "SUM(ISNULL(MulComplexQuest.MulPupaeWeight, 0.00)) MulPupaeWeight,   ";// --食用蛹、蛾重量
            StrAreaSql += "SUM(ISNULL(MulComplexQuest.MulPupaeValue, 0.00)) MulPupaeValue,    "; //--食用蛹、蛾产值
            StrAreaSql += "SUM(ISNULL(MulComplexQuest.MulWineWeight, 0.00)) MulWineWeight,   ";// --蚕蛾酒重量
            StrAreaSql += "SUM(ISNULL(MulComplexQuest.MulWineValue, 0.00)) MulWineValue,    "; //--蚕蛾酒产值
            StrAreaSql += "SUM(ISNULL(MulComplexQuest.OtherWeight, 0.00)) OtherWeight,    "; //--其他重量
            StrAreaSql += "SUM(ISNULL(MulComplexQuest.OtherValue, 0.00))   OtherValue   "; //--其他产值
            StrAreaSql += "from([dbo].[T_Report] Report left join T_MulComplexQuest MulComplexQuest on Report.Id = MulComplexQuest.ReportId)   ";
            StrAreaSql += "right join T_Area Area on Area.Code = Report.AreaCode   ";
            StrAreaSql += "where Area.ParentCode = @AreaCode  and Report.Status=@ReportStatus and Report.FillInYear=@FillInYear    ";
            StrAreaSql += "group by rollup(Area.Code)   ";
            StrAreaSql += ") tt1 left join T_Area Area on tt1.Code = Area.Code   ";
            StrAreaSql += "inner join T_Dict Dict  on Dict.GroupCode = 'ReportYear' and DictValue = @FillInYear ";
            StrAreaSql += "where area.ParentCode=@AreaCode  or area.Code=@AreaCode ";
            StrAreaSql += "order by AreaCode asc ";
            //市级统计代码
            string StrCitySql = "select Area.Code AreaCode,Dict.DictText FillYear, Area.FullName AreaName,Coord";
            StrCitySql += ", ISNULL(MulFunWeight,0.00) MulFunWeight,ISNULL( MulFunValue,0.00) MulFunValue, ISNULL(MulTeaWeight,0.00) MulTeaWeight,  ISNULL(MulTeaValue,0.00) MulTeaValue, ISNULL(MulFruitWeight,0.00) MulFruitWeight   ";
            StrCitySql += ",ISNULL(MulFruitValue,0.00) MulFruitValue, ISNULL(MulExcreWeight,0.00) MulExcreWeight, ISNULL(MulExcreValue,0.00) MulExcreValue, ISNULL(MulFertWeight,0.00) MulFertWeight, ISNULL(MulFertValue,0.00) MulFertValue ";
            StrCitySql += ",ISNULL(MulPupaeWeight,0.00) MulPupaeWeight, ISNULL(MulPupaeValue,0.00) MulPupaeValue, ISNULL(MulWineWeight,0.00) MulWineWeight, ISNULL(MulWineValue,0.00) MulWineValue, ISNULL(OtherWeight,0.00) OtherWeight , ISNULL(OtherValue,0.00) OtherValue   ";
            StrCitySql += "from(select   ";
            StrCitySql += "case when grouping(Area.ParentCode) = 1 then @AreaCode else ParentCode end ParentCode, ";
            StrCitySql += "SUM(ISNULL(MulComplexQuest.MulFunWeight, 0.00))  MulFunWeight,  "; //--桑枝菌重量
            StrCitySql += "SUM(ISNULL(MulComplexQuest.MulFunValue, 0.00)) MulFunValue,  "; //--桑枝菌产值
            StrCitySql += "SUM(ISNULL(MulComplexQuest.MulTeaWeight, 0.00)) MulTeaWeight,  "; //--桑叶茶、菜重量
            StrCitySql += "SUM(ISNULL(MulComplexQuest.MulTeaValue, 0.00)) MulTeaValue,  "; //--桑叶茶、菜产值
            StrCitySql += "SUM(ISNULL(MulComplexQuest.MulFruitWeight, 0.00))  MulFruitWeight,   "; //--桑果、汁、酒重量
            StrCitySql += "SUM(ISNULL(MulComplexQuest.MulFruitValue, 0.00)) MulFruitValue,  "; //--桑果、汁、酒产值
            StrCitySql += "SUM(ISNULL(MulComplexQuest.MulExcreWeight, 0.00)) MulExcreWeight,  "; //--蚕沙提取叶绿素重量
            StrCitySql += "SUM(ISNULL(MulComplexQuest.MulExcreValue, 0.00)) MulExcreValue, ";  //--蚕沙提取叶绿素产值
            StrCitySql += "SUM(ISNULL(MulComplexQuest.MulFertWeight, 0.00)) MulFertWeight,  "; //--蚕沙肥料重量
            StrCitySql += "SUM(ISNULL(MulComplexQuest.MulFertValue, 0.00)) MulFertValue,  "; //--蚕沙肥料产值
            StrCitySql += "SUM(ISNULL(MulComplexQuest.MulPupaeWeight, 0.00)) MulPupaeWeight,   "; //--食用蛹、蛾重量
            StrCitySql += "SUM(ISNULL(MulComplexQuest.MulPupaeValue, 0.00)) MulPupaeValue,   "; //--食用蛹、蛾产值
            StrCitySql += "SUM(ISNULL(MulComplexQuest.MulWineWeight, 0.00)) MulWineWeight,   "; //--蚕蛾酒重量
            StrCitySql += "SUM(ISNULL(MulComplexQuest.MulWineValue, 0.00)) MulWineValue,  "; //--蚕蛾酒产值
            StrCitySql += "SUM(ISNULL(MulComplexQuest.OtherWeight, 0.00)) OtherWeight,   ";//--其他重量
            StrCitySql += "SUM(ISNULL(MulComplexQuest.OtherValue, 0.00))   OtherValue   "; //--其他产值
            StrCitySql += "from t_report t1 ";
            StrCitySql += "left join T_MulComplexQuest MulComplexQuest on MulComplexQuest.ReportId = t1.Id ";
            StrCitySql += "right  join T_Area Area on Area.Code = t1.AreaCode ";
            StrCitySql += "where ParentCode != @AreaCode  and t1.Status=@ReportStatus and t1.FillInYear=@FillInYear   ";
            StrCitySql += "group by rollup(Area.ParentCode) ";
            StrCitySql += ") t left join T_Area  area on area.Code = t.ParentCode ";
            StrCitySql += "inner join T_Dict Dict  on Dict.GroupCode = 'ReportYear' and DictValue = @FillInYear ";
            StrCitySql += "where area.ParentCode=@AreaCode  or area.Code=@AreaCode ";
            StrCitySql += "order by AreaCode asc ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    var paramlist = new DynamicParameters();
                    paramlist.Add("AreaCode", model.AreaCode);
                    paramlist.Add("FillInYear", model.FillInYear);
                    paramlist.Add("ReportStatus", model.Status);
                    List<MulComplexStatsModel> list = new List<MulComplexStatsModel>();
                    if (model.AreaCode != ProvinceCode)//不为省级用户
                    {
                        list = dbCon.Query<MulComplexStatsModel>(StrAreaSql, paramlist).ToList();
                        
                    }
                    else
                    {
                        list = dbCon.Query<MulComplexStatsModel>(StrCitySql, paramlist).ToList();
                    }
                    result.model = list;
                    //List<MulComplexStatsModel> list2 = new List<MulComplexStatsModel>();
                    //list.Where(s => s.AreaCode == model.AreaCode).FirstOrDefault().AreaName = "合计";
                    //MulComplexStatsModel m = list.Where(s => s.AreaName == "合计").FirstOrDefault();
                    //list.Remove(m);
                    //list2.Add(m);
                    //for (var i = 0; i < list.Count; i++)
                    //{
                    //    list2.Add(list[i]);
                    //}
                    //result.model = list2;
                    if (list != null)
                    {
                        result.status = DefaultKey.SuccessResult;
                        result.message = "获取全年桑综合利用情况统计数据成功！";
                    }
                    else
                    {
                        result.status = DefaultKey.InfoResult;
                        result.message = "获取全年桑综合利用情况统计数据失败！";
                    }
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                result.status = DefaultKey.ErrorResult;
                result.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取全年桑综合利用情况统计数据失败", ex);
            }
            return result;
        }
        /// <summary>
        /// 获取时间趋势图数据列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<List<MulComplexStatsModel>> GetStatsByTimeList(MulNowModel model)
        {
            DictServices dictServices = new DictServices();
            List<Select> listDict = dictServices.GetListByGroupCode("ReportYear").model;
            ResResult<List<MulComplexStatsModel>> result = new ResResult<List<MulComplexStatsModel>>();
            List<MulComplexStatsModel> list = new List<MulComplexStatsModel>();
            if (listDict.Count > 0)
            {
                if (!string.IsNullOrEmpty(model.AreaCode))
                {
                    for (var i = 0; i < listDict.Count; i++)
                    {
                        model.FillInYear = listDict[i].label;
                        List<MulComplexStatsModel> listOne = GetStatsList(model).model;
                        if (listOne.Count > 0)
                        {
                            list.Add(listOne[0]);
                        }
                    }
                    if (list.Count > 0)
                    {
                        result.message = "时间趋势数据列表获取成功";
                        result.model = list.OrderBy(s => s.FillYear).ToList();
                        result.status = DefaultKey.SuccessResult;
                    }
                    else
                    {
                        result.message = "时间趋势数据列表为空";
                        result.status = DefaultKey.InfoResult;
                    }
                }
                else
                {
                    result.message = "时间趋势数据列表失败,当前地区不存在";
                    result.status = DefaultKey.ErrorResult;
                }
            }
            else
            {
                result.message = "时间趋势数据列表失败,时间为空";
                result.status = DefaultKey.ErrorResult;
            }
            return result;
        }
        ///获取统计数据详情
        public ResResult<List<MulComplexStatsModel>> GetDetailStats(MulNowModel model)
        {
            ResResult<List<MulComplexStatsModel>> res = new ResResult<List<MulComplexStatsModel>>();
            List<MulComplexStatsModel> list = new List<MulComplexStatsModel>();
            string ProvinceCode = DefaultKey.ProvinceCode;
            string FillInYear = model.FillInYear;
            try
            {
                if (model.AreaCode == ProvinceCode)
                {
                    var First = GetStatsList(model).model;
                    //First[0].AreaName = "合计";
                    list.Add(First[0]);//省级汇总合计
                    foreach (var item in First)
                    {
                        if (item.AreaCode != ProvinceCode)
                        {
                            MulNowModel model2 = new MulNowModel();
                            model2.FillInYear = FillInYear;
                            model2.AreaCode = item.AreaCode;
                            model2.Status = model.Status;
                            var Second = GetStatsList(model2).model;
                            list.Add(item);
                            foreach (var item2 in Second)
                            {
                                if (model2.AreaCode != item2.AreaCode)
                                {
                                    list.Add(item2);
                                }
                            }
                        }
                    }
                }
                else
                {
                    list = GetStatsList(model).model;//数据获取
                }
            }
            catch (Exception ex)
            {
                res.model = list;
                res.message = "获取统计数据详情失败" + ex.ToString().Trim();
                res.status = DefaultKey.SuccessResult;
            }
            if (list != null)
            {
                res.model = list;
                res.message = "获取统计数据详情成功";
                res.status = DefaultKey.SuccessResult;
            }
            else
            {
                res.message = "获取统计数据详情成失败";
                res.status = DefaultKey.ErrorResult;
            }
            return res;
        }
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MemoryStream Method(MulNowModel model)
        {
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\Excel.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("MulComplexQuest") as HSSFSheet;//读取模板所在sheet位置
            MulComplexViewModel pagelist = GetModel(model).model;//数据获取

            HSSFRow row = (HSSFRow)sheet.GetRow(3);//读取模板第四行
            var style = row.GetCell(0).CellStyle;//读取模板第二行第一单元格样式并保存
            row.GetCell(0).CellStyle = style;//对第二行第一单元格设置原来样式

            HSSFRow datarow = (HSSFRow)sheet.CreateRow(4);
            datarow.CreateCell(0).SetCellValue(pagelist.MulFunWeight == null ? "" : Common.GetOne(pagelist.MulFunWeight));
            datarow.CreateCell(1).SetCellValue(pagelist.MulFunValue == null ? "" : Common.GetOne(pagelist.MulFunValue));
            datarow.CreateCell(2).SetCellValue(pagelist.MulTeaWeight == null ? "" : Common.GetOne(pagelist.MulTeaWeight));
            datarow.CreateCell(3).SetCellValue(pagelist.MulTeaValue == null ? "" : Common.GetOne(pagelist.MulTeaValue));
            datarow.CreateCell(4).SetCellValue(pagelist.MulFruitWeight == null ? "" : Common.GetOne(pagelist.MulFruitWeight));
            datarow.CreateCell(5).SetCellValue(pagelist.MulFruitValue == null ? "" : Common.GetOne(pagelist.MulFruitValue));
            datarow.CreateCell(6).SetCellValue(pagelist.MulExcreWeight == null ? "" : Common.GetOne(pagelist.MulExcreWeight));
            datarow.CreateCell(7).SetCellValue(pagelist.MulExcreValue == null ? "" : Common.GetOne(pagelist.MulExcreValue));
            datarow.CreateCell(8).SetCellValue(pagelist.MulFertWeight == null ? "" : Common.GetOne(pagelist.MulFertWeight));
            datarow.CreateCell(9).SetCellValue(pagelist.MulFertValue == null ? "" : Common.GetOne(pagelist.MulFertValue));
            datarow.CreateCell(10).SetCellValue(pagelist.MulPupaeWeight == null ? "" : Common.GetOne(pagelist.MulPupaeWeight));
            datarow.CreateCell(11).SetCellValue(pagelist.MulPupaeValue == null ? "" : Common.GetOne(pagelist.MulPupaeValue));
            datarow.CreateCell(12).SetCellValue(pagelist.MulWineWeight == null ? "" : Common.GetOne(pagelist.MulWineWeight));
            datarow.CreateCell(13).SetCellValue(pagelist.MulWineValue == null ? "" : Common.GetOne(pagelist.MulWineValue));
            datarow.CreateCell(14).SetCellValue(pagelist.OtherWeight == null ? "" : Common.GetOne(pagelist.OtherWeight));
            datarow.CreateCell(15).SetCellValue(pagelist.OtherValue == null ? "" : Common.GetOne(pagelist.OtherValue));
            for (var i = 0; i <= 15; i++)
            {
                datarow.GetCell(i).CellStyle = style;
            }
            sheet.CopyTo(workbook, "Sheet1", true, true);
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }
        /// <summary>
        /// 导出统计数据
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MemoryStream MethodStats(MulNowModel model)
        {
            string FillInYear = model.FillInYear;
            AreaServices areaServices = new AreaServices();
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string AreaName = areaServices.GetByCodeModel(model.AreaCode).model.Name == "云南省" ? "云南省_县(市、州)" : areaServices.GetByCodeModel(model.AreaCode).model.Name;//获取当前行政区划名称
            getExcelStatsList(workbook, GetDetailStats(model).model, AreaName);//省级汇总(针对曲线)
            if (model.AreaCode == DefaultKey.ProvinceCode)//判断当前用户是否为省级用户
            {
                AreaName = areaServices.GetByCodeModel(model.AreaCode).model.Name;
                model.FillInYear = FillInYear;
                getExcelStatsList(workbook, GetStatsList(model).model, AreaName + "_州市");//省级汇总(针对州市)
                var areaList = areaServices.GetPrefectureList();//获取州市级列表
                foreach (var areaCode in areaList)
                {
                    model.AreaCode = areaCode;
                    model.FillInYear = FillInYear;
                    getExcelStatsList(workbook, GetDetailStats(model).model, areaServices.GetByCodeModel(areaCode).model.Name);
                }
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }
        /// <summary>
        /// 返回excel工作簿
        /// </summary>
        /// <param name="workbook">工作簿</param>
        /// <param name="sheet">子表</param>
        /// <param name="model"></param>
        /// <param name="AreaName">地区名称</param>
        /// <returns></returns>
        public HSSFWorkbook getExcelStatsList(HSSFWorkbook workbook, List<MulComplexStatsModel> list, string AreaName)
        {
            FileStream file = null;
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\ExcelStats.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("MulComplexQuest") as HSSFSheet;//读取模板所在sheet位置
            //List<VegBasicInfoStatsModel> list = new List<VegBasicInfoStatsModel>();
            //list = null;
            //list = GetDetailStats(model).model;
            #region 县市级表格样式
            HSSFRow row = (HSSFRow)sheet.GetRow(1);//读取模板第二行
            var style = row.GetCell(0).CellStyle;//读取模板第二行第一单元格样式并保存
            #endregion
            //row.GetCell(0).CellStyle = style;//对第二行第一单元格设置原来样式
            #region 州市级表格样式
            //var style2 = row.GetCell(1).CellStyle;
            var styleCityOne = Common.GetOneCityCellStyle(templateBook, style);
            var styleAreaOne = Common.GetOneAreaCellStyle(templateBook, style);
            #endregion
            if (list != null)
            {
                for (int i = 0; i < list.Count; i++)
                {
                    HSSFRow datarow = (HSSFRow)sheet.CreateRow(i + 4);
                    datarow.CreateCell(0).SetCellValue(list[i].AreaName == null ? "" : list[i].AreaName.ToString());
                    datarow.CreateCell(1).SetCellValue(list[i].MulFunWeight == null ? 0.0 : Convert.ToDouble(list[i].MulFunWeight));
                    datarow.CreateCell(2).SetCellValue(list[i].MulFunValue == null ? 0 : Convert.ToDouble(list[i].MulFunValue));
                    datarow.CreateCell(3).SetCellValue(list[i].MulTeaWeight == null ? 0 : Convert.ToDouble(list[i].MulTeaWeight));
                    datarow.CreateCell(4).SetCellValue(list[i].MulTeaValue == null ? 0 : Convert.ToDouble(list[i].MulTeaValue));
                    datarow.CreateCell(5).SetCellValue(list[i].MulFruitWeight == null ? 0 : Convert.ToDouble(list[i].MulFruitWeight));
                    datarow.CreateCell(6).SetCellValue(list[i].MulFruitValue == null ? 0 : Convert.ToDouble(list[i].MulFruitValue));
                    datarow.CreateCell(7).SetCellValue(list[i].MulExcreWeight == null ? 0 : Convert.ToDouble(list[i].MulExcreWeight));
                    datarow.CreateCell(8).SetCellValue(list[i].MulExcreValue == null ? 0 : Convert.ToDouble(list[i].MulExcreValue));
                    datarow.CreateCell(9).SetCellValue(list[i].MulFertWeight == null ? 0 : Convert.ToDouble(list[i].MulFertWeight));
                    datarow.CreateCell(10).SetCellValue(list[i].MulFertValue == null ? 0 : Convert.ToDouble(list[i].MulFertValue));
                    datarow.CreateCell(11).SetCellValue(list[i].MulPupaeWeight == null ? 0 : Convert.ToDouble(list[i].MulPupaeWeight));
                    datarow.CreateCell(12).SetCellValue(list[i].MulPupaeValue == null ? 0 : Convert.ToDouble(list[i].MulPupaeValue));
                    datarow.CreateCell(13).SetCellValue(list[i].MulWineWeight == null ? 0 : Convert.ToDouble(list[i].MulWineWeight));
                    datarow.CreateCell(14).SetCellValue(list[i].MulWineValue == null ? 0 : Convert.ToDouble(list[i].MulWineValue));
                    datarow.CreateCell(15).SetCellValue(list[i].OtherWeight == null ? 0 : Convert.ToDouble(list[i].OtherWeight));
                    datarow.CreateCell(16).SetCellValue(list[i].OtherValue == null ? 0 : Convert.ToDouble(list[i].OtherValue));
                    if (CustomFun.IsCity(list[i].AreaCode))
                    {//省级和州市级
                        for (int j = 0; j < 17; j++)
                        {
                            datarow.GetCell(j).CellStyle = styleCityOne;
                        }
                    }
                    else//县级
                    {
                        for (int j = 0; j < 17; j++)
                        {
                            datarow.GetCell(j).CellStyle = styleAreaOne;
                        }
                    }
                }
                sheet.CopyTo(workbook, AreaName, true, true);
            }
            return workbook;
        }
    }
}